/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Bean.ApplicantBean;
import Bean.EmployeeBean;
import Utility.DBProcess;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
//import org.apache.struts.action.ActionErrors;
//import org.apache.struts.action.ActionMessage;

/**
 *
 * @author Vietlt
 */
public class EmployeeDAO {

    public static void main(String[] args) {
//LoadAll
//        List<EmployeeBean> listEmp = new EmployeeDAO().LoadAllEmployee();
//        for (EmployeeBean emp : listEmp) {
//            System.out.println("ID :" + emp.getEmployeeID());
//            System.out.println("DepartID :" + emp.getDepartmentID());
//            System.out.println("First name :" + emp.getFirstName());
//        }
        //LoadById
//        EmployeeBean empB = new EmployeeDAO().LoadAllEmployeeById(1);
//        System.out.println("ID :" + empB.getEmployeeID());
//        System.out.println("DepartID :" + empB.getDepartmentID());
//        System.out.println("First name :" + empB.getFirstName());
//        //Add New
//        EmployeeBean empB = new EmployeeBean();
//        empB.setDepartmentID(1);
//        empB.setFirstName("Nguyen");
//        empB.setLastName("Huy Binh");
//        empB.setGender(false);
//        empB.setDOB("1/1/1991");
//        empB.setPhoneNumber("0298353423");
//        empB.setEmail("huybinh91@yahoo.com");
//        empB.setAddress("72 Hang Giay");
//        empB.setPosition("Employee");
//        empB.setAccount("huybinh91");
//        empB.setPassword("123");
//        int rs = new EmployeeDAO().AddNewEmployee(empB);
//        if (rs == 1) {
//            System.out.println("Add OKK");
//        }

        //Update
//        EmployeeBean empB = new EmployeeBean();
//        empB.setDepartmentID(1);
//        empB.setFirstName("Nguyen2");
//        empB.setLastName("Huy Binh2");
//        empB.setGender(false);
//        empB.setDOB("1/1/1991");
//        empB.setPhoneNumber("02983534223");
//        empB.setEmail("Biboy@yahoo.com");
//        empB.setAddress("So8 Ngach Ho");
//        empB.setPosition("Em2ployee");
//        empB.setAccount("huybinh9111");
//        empB.setPassword("1223");
//        empB.setEmployeeID(5);
//        int rs = new EmployeeDAO().UpdateEmployee(empB);
//        if (rs == 1) {
//            System.out.println("Update OKkkkkkkkkkkkkkkkkkkkK");
//        }
        //Delete
//        int rs = new EmployeeDAO().DeleteEmployee(6);
//        if (rs == 1) {
//            System.out.println("Delete OKkkkkkkkkkkkkkkkkkkkK");
//        } else {
//            System.out.println("Noooooooooooooooooooooooooooooooo");
//        }

//        Check Account
        String empB = new EmployeeDAO().validateAccount("acc");
        System.out.println(empB);
    }

    public List<EmployeeBean> LoadAllEmployee() {
        List<EmployeeBean> list = new ArrayList<EmployeeBean>();
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        try {
            stm = conn.prepareCall("{ call Employee_getall}");
            rs = stm.executeQuery();
            while (rs.next()) {
                EmployeeBean empB = new EmployeeBean();
                empB.setEmployeeID(rs.getInt("EmployeeID"));
                empB.setDepartmentID(rs.getInt("DepartmentID"));
                empB.setFirstName(rs.getString("FirstName"));
                empB.setLastName(rs.getString("LastName"));
                empB.setGender(rs.getBoolean("Gender"));
                empB.setDOB(rs.getString("DOB"));
                empB.setPhoneNumber(rs.getString("PhoneNumber"));
                empB.setEmail(rs.getString("Email"));
                empB.setAddress(rs.getString("Address"));
                empB.setPosition(rs.getString("Position"));
                empB.setAccount(rs.getString("Account"));
                empB.setPassword(rs.getString("Password"));
                list.add(empB);
                if (list.isEmpty()) {
                    String err = "List empty !!!";
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return list;
    }

    public EmployeeBean LoadAllEmployeeById(int empID) {
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        EmployeeBean empB = new EmployeeBean();
        try {
            stm = conn.prepareCall("{ call Employee_get (?)}");
            stm.setInt(1, empID);
            rs = stm.executeQuery();
            while (rs.next()) {
                empB.setEmployeeID(rs.getInt("EmployeeID"));
                empB.setDepartmentID(rs.getInt("DepartmentID"));
                empB.setFirstName(rs.getString("FirstName"));
                empB.setLastName(rs.getString("LastName"));
                empB.setGender(rs.getBoolean("Gender"));
                empB.setDOB(rs.getString("DOB"));
                empB.setPhoneNumber(rs.getString("PhoneNumber"));
                empB.setEmail(rs.getString("Email"));
                empB.setAddress(rs.getString("Address"));
                empB.setPosition(rs.getString("Position"));
                empB.setAccount(rs.getString("Account"));
                empB.setPassword(rs.getString("Password"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return empB;
    }

    public int AddNewEmployee(EmployeeBean empB) {
//        String Flag = null;
//        ActionErrors errors = new ActionErrors();
//        empB = new EmployeeBean();       
//        String DepartmentID = empB.getDepartmentID();
//        String FirstName = empB.getFirstName();
//        String LastName = empB.getLastName();
//        String DOB = empB.getDOB();
//        String PhoneNumber = empB.getPhoneNumber();
//        String Email = empB.getEmail();
//        String Address = empB.getAddress();
//        String Position = empB.getPosition();
//        String Account = empB.getAccount();
//        String Password = empB.getPassword();
//        if (DepartmentID.trim().length()==0) {
//            errors.add("DepartmentID", new ActionMessage("erDepartmentID"));
//            Flag = "ErEmployee";
//        }
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        int isComplete = 0;
        try {
            String dob = empB.getDOB();
            SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
            Date dDob = format.parse(dob);
            stm = conn.prepareCall("{ call Employee_add (?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt(1, empB.getDepartmentID());
            stm.setString(2, empB.getFirstName());
            stm.setString(3, empB.getLastName());
            stm.setBoolean(4, empB.isGender());
            stm.setDate(5, new java.sql.Date(dDob.getTime()));
            stm.setString(6, empB.getPhoneNumber());
            stm.setString(7, empB.getEmail());
            stm.setString(8, empB.getAddress());
            stm.setString(9, empB.getPosition());
            stm.setString(10, empB.getAccount());
            stm.setString(11, empB.getPassword());
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return isComplete;
    }

    public int UpdateEmployee(EmployeeBean emB) {
        int isComplete = 0;
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        String dob = emB.getDOB();
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        try {
            Date dDob = format.parse(dob);
            stm = conn.prepareCall("{ call Employee_update (?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setInt(1, emB.getEmployeeID());
            stm.setInt(2, emB.getDepartmentID());
            stm.setString(3, emB.getFirstName());
            stm.setString(4, emB.getLastName());
            stm.setBoolean(5, emB.isGender());
            stm.setDate(6, new java.sql.Date(dDob.getTime()));
            stm.setString(7, emB.getPhoneNumber());
            stm.setString(8, emB.getEmail());
            stm.setString(9, emB.getAddress());
            stm.setString(10, emB.getPosition());
            stm.setString(11, emB.getAccount());
            stm.setString(12, emB.getPassword());

            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 2;
            }
        } catch (ParseException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return isComplete;
    }

    public int DeleteEmployee(int empID) {
        int isComplete = 0;
        Connection conn = null;
        CallableStatement stm = null;
        conn = DBProcess.getConnection();
        try {
            stm = conn.prepareCall("{ call Employee_delete (?)}");
            stm.setInt(1, empID);
            if (stm.executeUpdate() == 1) {
                isComplete = 1;
            } else {
                isComplete = 0;
            }
        } catch (Exception ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return isComplete;
    }

    public String validateAccount(String empAccount) {
        Connection conn = null;
        CallableStatement stm = null;
        ResultSet rs = null;
        conn = DBProcess.getConnection();
        String Flag = null;
        EmployeeBean empB = new EmployeeBean();
        try {
            stm = conn.prepareCall("{ call Employee_ValiAc (?)}");
            stm.setString(1, empAccount);
            rs = stm.executeQuery();
            if (rs.next()) {
                Flag = "Trung Account";
            } else {
                Flag = "Ko Trung Account";
            }
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBProcess.closeResultSet(rs);
            DBProcess.closeStatement(stm);
            DBProcess.closeConnection(conn);
        }
        return Flag;
    }
}
